-- @owner: @zhanghuan96
-- @date: 2024/11/12
-- @testpoint: 使用函数解析存储过程
@conn PrimaryDbAmode;
--前置条件
drop extension if exists gms_utility;
create extension gms_utility;
--step1:创建存储过程;expect:成功
drop procedure if exists proc_resolve0013;
CREATE OR REPLACE PROCEDURE public.proc_resolve0013(name VARCHAR2(100)) IS
	id INT := 0;
BEGIN
	SELECT public.t_seq.NEXTVAL INTO id;
	INSERT INTO public.t_resolve VALUES (id, name);
END;
/
--step2:解析存储过程;expect:成功，part2是存储过程名，解析参数schema、part2、part1_type为7、object_number
declare
    name varchar2 := 'public.proc_resolve0013';
    context number := 1;
    schema  varchar2;
    part1   varchar2;
    part2   varchar2;
    dblink  varchar2;
    part1_type  number;
    object_number   number;
begin
    gms_utility.NAME_RESOLVE(name, context, schema, part1, part2, dblink, part1_type, object_number);
    raise info 'INFO:schema = %, part1 = %, part2 = %, dblink = %, part1_type = %, object_number = %', schema, part1, part2, dblink, part1_type, object_number;
end;
/
--step3:不带schema;expect:成功
declare
    name varchar2 := 'proc_resolve0013';
    context number := 1;
    schema  varchar2;
    part1   varchar2;
    part2   varchar2;
    dblink  varchar2;
    part1_type  number;
    object_number   number;
begin
    gms_utility.NAME_RESOLVE(name, context, schema, part1, part2, dblink, part1_type, object_number);
    raise info 'INFO:schema = %, part1 = %, part2 = %, dblink = %, part1_type = %, object_number = %', schema, part1, part2, dblink, part1_type, object_number;
end;
/
--step4:创建存储过程的同义词;expect:成功
drop synonym if exists syn_pro;
CREATE OR REPLACE SYNONYM public.syn_pro FOR proc_resolve0013;
--step5:解析;expect:成功
declare
    name varchar2 := 'public.syn_pro';
    context number := 1;
    schema  varchar2;
    part1   varchar2;
    part2   varchar2;
    dblink  varchar2;
    part1_type  number;
    object_number   number;
begin
    gms_utility.NAME_RESOLVE(name, context, schema, part1, part2, dblink, part1_type, object_number);
    raise info 'INFO:schema = %, part1 = %, part2 = %, dblink = %, part1_type = %, object_number = %', schema, part1, part2, dblink, part1_type, object_number;
end;
/
--step6:不带schema;expect:成功
declare
    name varchar2 := 'syn_pro';
    context number := 1;
    schema  varchar2;
    part1   varchar2;
    part2   varchar2;
    dblink  varchar2;
    part1_type  number;
    object_number   number;
begin
    gms_utility.NAME_RESOLVE(name, context, schema, part1, part2, dblink, part1_type, object_number);
    raise info 'INFO:schema = %, part1 = %, part2 = %, dblink = %, part1_type = %, object_number = %', schema, part1, part2, dblink, part1_type, object_number;
end;
/
--step7:清理环境;expect:成功
drop procedure if exists proc_resolve0013;
drop synonym if exists syn_pro;
drop extension if exists gms_utility;